/*
 * Database.java
 */
package ITU_ShuttleTracker;

import ITU_ShuttleTracker.User.Location.Location;
import ITU_ShuttleTracker.User.Shuttle.Route;
import ITU_ShuttleTracker.User.Shuttle.Schedule;
import ITU_ShuttleTracker.User.ShuttleBus.ShuttleBus;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.primefaces.model.map.LatLng;

/**
 *
 * @author Onuralp
 */
public class Database {
    private Connection connection = null;
    String databaseUrl;
    String username;
    String password;

    public Database(){    
        this.databaseUrl= "jdbc:mysql://localhost:3306/itu_shuttletracker?";
        this.username= "root";
        this.password= "23072348290";        
    }

    /**
     * Connection method for connecting the database
     * @throws Exception Connection exception 
     */
    public void connect() throws Exception{
        //If there is a connection return true
        if ((this.connection!=null)) {
            if (this.connection.isClosed() == false){
                return;
            }
        }
        try {
            // If there is no connection create again
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String connectionURL = this.databaseUrl+"user="+this.username+"&password="+this.password;
            System.out.println(connectionURL);
            this.connection = DriverManager.getConnection(connectionURL);
        } catch (SQLException e) {
            System.out.println("SQL Exception: "+ e.toString());
        } catch (ClassNotFoundException cE) {
            System.out.println("Class Not Found Exception: "+ cE.toString());
        }
    }

    /**
     * Disconnection method for disconnecting the database
     * @throws SQLException Disconnect sql exception
     */
    public void disconnect() throws SQLException{
        if ((this.connection!=null)) {
            if (!this.connection.isClosed()) {
                this.connection.close();
            }
        }
    }
    
    /**
     * Select route method
     * @return Route
     * @throws SQLException Route SQL exception 
     */
    public Route selectRoute() throws SQLException {
        String query = "SELECT * FROM route";
        Statement statement = this.connection.createStatement();
        ResultSet result = statement.executeQuery(query);
        Route route = new Route();
        while (result.next()) {
            double x = result.getDouble("locationX");
            double y = result.getDouble("locationY");
            String stationName = result.getString("stationName");        
            Location location = new Location(stationName, x, y);
            route.getStationList().add(location);
        }
        return route;
    }

    /**
     * Select schedule method
     * @return Schedule entry list
     * @throws SQLException Schedule SQL exception 
     */
    public List<Schedule> selectSchedule() throws SQLException {
        String query = "SELECT * FROM shuttle_bus_schedule";
        Statement statement = this.connection.createStatement();
        ResultSet result = statement.executeQuery(query);
        List<Schedule> scheduleList = new ArrayList<Schedule>();
        while (result.next()) {
            Schedule schedule = new Schedule();
            schedule.setScheduleID(result.getInt("scheduleID"));
            schedule.setShuttleID(result.getInt("shuttleID"));
            Timestamp startTime = result.getTimestamp("startTime");
            long milliseconds = startTime.getTime() + (startTime.getNanos() / 1000000);
            schedule.setStartTime(new Date(milliseconds));
            Timestamp arrivalTime = result.getTimestamp("arrivalTime");
            milliseconds = arrivalTime.getTime() + (arrivalTime.getNanos() / 1000000);
            schedule.setArrivalTime(new Date(milliseconds));
            scheduleList.add(schedule);
        }
        return scheduleList;
    }
    
    /**
     * Select GPS data method
     * @return GPS data list
     * @throws SQLException GPS SQL Exception
     */
    public List<LatLng> selectGPS() throws SQLException {
        String query = "SELECT * FROM gps";
        Statement statement = this.connection.createStatement();
        ResultSet result = statement.executeQuery(query);
        List<LatLng> gpsList = new ArrayList<LatLng>();
        while (result.next()) {
            double x = result.getDouble("lat");
            double y = result.getDouble("lng");
            gpsList.add(new LatLng(x, y));
        }
        return gpsList;
    }
    
    /**
     * Add shuttle bus method
     * @param shuttleBus Shuttle bus
     */
    public void addShuttleBus(ShuttleBus shuttleBus) {
    	try {
            String query = "INSERT INTO shuttle_bus(information, shuttleNumber, shuttlePicture) VALUES (?, ?, ?)";
            PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setString(1, shuttleBus.getInformation());
            commit.setInt(2, shuttleBus.getShuttleNo());
            commit.setString(3, shuttleBus.getPicture());
            commit.executeUpdate();
        }
        catch (SQLException error) {
            throw new UnsupportedOperationException(error.getMessage());
        }
    }
    
    /**
     * Update shuttle bus method
     * @param shuttleBus Shuttle bus
     */
    public void updateShuttleBus(ShuttleBus shuttleBus) {
    	try {
            String query = "UPDATE shuttle_bus SET shuttleNumber=?, information=?, shuttlePicture=? WHERE (shuttleID=?)";
            PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setInt(1, shuttleBus.getShuttleNo());
            commit.setString(2, shuttleBus.getInformation());
            commit.setString(3, shuttleBus.getPicture());
            commit.setInt(4, shuttleBus.getShuttleID());
            commit.executeUpdate();
        }
        catch (SQLException error) {
            throw new UnsupportedOperationException(error.getMessage());
        }
    }
    
    /**
     * Delete shuttle bus method
     * @param shuttleBus Shuttle bus
     */
    public void deleteShuttleBus(ShuttleBus shuttleBus) {
    	try {
            String query = "DELETE FROM shuttle_bus WHERE (shuttleID=?)";
            PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setInt(1, shuttleBus.getShuttleID());
            commit.executeUpdate();
        }
        catch (SQLException error) {
            throw new UnsupportedOperationException(error.getMessage());
        }
    }
    
    /**
     * Select shuttle bus method
     * @param shuttleID Shuttle ID
     * @return Shuttle bus
     */
    public ShuttleBus selectShuttleBus(int shuttleID) {
    	try {
            String query = "SELECT * FROM shuttle_bus WHERE (shuttleID=?)";
            PreparedStatement commit = this.connection.prepareStatement(query);
            commit.setInt(1, shuttleID);
            ResultSet result = commit.executeQuery();
            ShuttleBus shuttleBus = new ShuttleBus(); 
            if (result.next()) {
                shuttleBus.setShuttleID(result.getInt("shuttleID"));
            	shuttleBus.setInformation(result.getString("information"));
                shuttleBus.setShuttleNo(result.getInt("shuttleNumber"));
                shuttleBus.setPicture(result.getString("shuttlePicture"));
            }
            return shuttleBus; 
        }
        catch (SQLException error) {
            throw new UnsupportedOperationException(error.getMessage());
        }
    }
    
    /**
     * Select shuttle bus method
     * @return Shuttle bus list
     */
    public List<ShuttleBus> selectShuttleBus() {
    	List<ShuttleBus> shuttleBusList = new ArrayList<ShuttleBus>();  
    	try {
            String query = "SELECT * FROM shuttle_bus";
            Statement commit = this.connection.createStatement();
            ResultSet result = commit.executeQuery(query);
            while (result.next()) {
                ShuttleBus shuttleBus = new ShuttleBus();
            	shuttleBus.setShuttleID(result.getInt("shuttleID"));
            	shuttleBus.setInformation(result.getString("information"));
                shuttleBus.setShuttleNo(result.getInt("shuttleNumber"));
                shuttleBus.setPicture(result.getString("shuttlePicture"));
                shuttleBusList.add(shuttleBus);
            }
            return shuttleBusList; 
        }
        catch (SQLException error) {
            throw new UnsupportedOperationException(error.getMessage());
        }
    }
}
